﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using System.Xml;
using Common.Utilities;

namespace Workflows.Components.ETL
{
    /// <summary>
    /// <DbSchemas>
    ///     <Connection Name="" ConnectionString="" Provider="0">
    ///         <Schema Name="">
    ///             <Table>
    ///                 <Column></Column>
    ///             </Table>
    ///             <View>
    ///                 <Column></Column>
    ///             </View>
    ///         </Schema>
    ///         <SelectSql>
    ///             <Column></Column>
    ///         </SelectSql>
    ///     </Connection>
    /// </DbSchemas>
    /// </summary>
    public class DbSchemaCache
    {
        /// <summary>
        /// 
        /// </summary>
        public struct DbSchemaCacheKey
        {
            public DataProviderType DbProvider;
            public string ConnStr;
            public string SchemaName;
            public string TableName;
            public string SelectStatement;
        }

        private string _SchemaFilePath;
        /// <summary>
        /// 
        /// </summary>
        public string SchemaFilePath
        {
            get
            {
                return this._SchemaFilePath;
            }
            set
            {
                this._SchemaFilePath = value;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public DbSchemaCache()
        {
            string appDataFolderPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
            appDataFolderPath = Path.Combine(appDataFolderPath, "Workflow Data");
            if(!Directory.Exists(appDataFolderPath))
            {
                Directory.CreateDirectory(appDataFolderPath);
            }
            SchemaFilePath = Path.Combine(appDataFolderPath, "DbSchemaCache.xml");
            if(!File.Exists(this.SchemaFilePath))
            {
                XmlDocument xDoc=new XmlDocument();
                xDoc.LoadXml("<DbSchemas></DbSchemas>");
                xDoc.Save(this.SchemaFilePath);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <param name="tableNames"></param>
        /// <param name="tblSchemas"></param>
        public void StoreDbSchema(
            DbSchemaCacheKey cacheKey, 
            List<string> tableNames, 
            Dictionary<string,List<TableColumn>> tblSchemas)
        {
            
            XmlDocument xDoc=new XmlDocument();
            xDoc.Load(this.SchemaFilePath);
            if (string.IsNullOrEmpty(cacheKey.ConnStr))
            {
                throw new Exception("DBSchema cache key must contain connection string");
            }
            XmlNode connNode =
                xDoc.SelectSingleNode("//Connection[@ConnectionString=\"" +
                                      cacheKey.ConnStr + "\"]");
            if (connNode == null)
            {
                XmlNode root = xDoc.DocumentElement;
                connNode = XmlDataUtil.AddElement(ref xDoc, root, "Connection");
                XmlDataUtil.UpdateAttribute(ref xDoc, connNode, "Name", "");
                XmlDataUtil.UpdateAttribute(ref xDoc, connNode, "ConnectionString", cacheKey.ConnStr);
                XmlDataUtil.UpdateAttribute(ref xDoc, connNode, "Provider", ((int) cacheKey.DbProvider).ToString());
            }

            XmlNode tableRootNode = null;
            if (string.IsNullOrEmpty(cacheKey.SelectStatement))
            {
                if (string.IsNullOrEmpty(cacheKey.SchemaName))
                    cacheKey.SchemaName = string.Empty;
                XmlNode schemaNode = connNode.SelectSingleNode("Schema[@Name=\"" + cacheKey.SchemaName + "\"]");
                if (schemaNode == null)
                {
                    schemaNode = XmlDataUtil.AddElement(ref xDoc, connNode, "Scehma");
                    XmlDataUtil.UpdateAttribute(ref xDoc, schemaNode, "Name", cacheKey.SchemaName);
                }
                tableRootNode = schemaNode;
            }
            else 
            {
                XmlNode sqlNode = connNode.SelectSingleNode("SelectSql[@Sql=\"" +
                                                            cacheKey.SelectStatement + "\"]");
                if (sqlNode == null)
                {
                    sqlNode = XmlDataUtil.AddElement(ref xDoc, connNode, "SelectSql");
                    XmlDataUtil.UpdateAttribute(ref xDoc, sqlNode, "Sql", cacheKey.SelectStatement);
                }
                tableRootNode = sqlNode;
            }
            if (tableNames != null && tableNames.Count > 0)
            {
                foreach (string tblName in tableNames)
                {
                    XmlNode tableNode = tableRootNode.SelectSingleNode("Table[@Name=\"" + tblName + "\"]");
                    if (tableNode == null)
                    {
                        tableNode = XmlDataUtil.AddElement(ref xDoc, tableRootNode, "Table");
                    }
                    if (tblSchemas !=null && tblSchemas.ContainsKey(tblName))
                    {
                        tableNode.RemoveAll();
                        XmlDataUtil.UpdateAttribute(ref xDoc, tableNode, "Name", tblName);
                        List<TableColumn> cols = tblSchemas[tblName];
                        foreach (TableColumn col in cols)
                        {
                            XmlNode colNode = XmlDataUtil.AddElement(ref xDoc, tableNode, "Column");
                            XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "ColumnName", col.ColumnName);
                            XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "DataType", ((int)col.DbType).ToString());
                            XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "Size", col.Size.ToString());
                        }
                    }
                    else
                    {
                        XmlDataUtil.UpdateAttribute(ref xDoc, tableNode, "Name", tblName);
                    }
                }
            }
            xDoc.Save(this.SchemaFilePath);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <returns></returns>
        public Dictionary<string,List<TableColumn>> GetDbSchema(DbSchemaCacheKey cacheKey)
        {
            Dictionary<string,List<TableColumn>> dbSchema=new Dictionary<string, List<TableColumn>>();
            if (string.IsNullOrEmpty(cacheKey.ConnStr))
            {
                throw new Exception("DBSchema cache key must contain connection string");
            }
            XmlDocument xDoc = new XmlDocument();
            xDoc.Load(this.SchemaFilePath);
            XmlNode connNode =
                xDoc.SelectSingleNode("//Connection[@ConnectionString=\"" +
                                      cacheKey.ConnStr + "\"]");
            if (connNode == null)
            {
                XmlNode root = xDoc.DocumentElement;
                connNode = XmlDataUtil.AddElement(ref xDoc, root, "Connection");
                XmlDataUtil.UpdateAttribute(ref xDoc, connNode, "Name", "");
                XmlDataUtil.UpdateAttribute(ref xDoc, connNode, "ConnectionString", cacheKey.ConnStr);
                XmlDataUtil.UpdateAttribute(ref xDoc, connNode, "Provider", ((int)cacheKey.DbProvider).ToString());
            }
            XmlNode tableRootNode = null;
            if (string.IsNullOrEmpty(cacheKey.SelectStatement))
            {
                if (string.IsNullOrEmpty(cacheKey.SchemaName))
                    cacheKey.SchemaName = string.Empty;
                XmlNode schemaNode = connNode.SelectSingleNode("Schema[@Name=\"" + cacheKey.SchemaName + "\"]");
                if (schemaNode == null)
                {
                    schemaNode = XmlDataUtil.AddElement(ref xDoc, connNode, "Scehma");
                    XmlDataUtil.UpdateAttribute(ref xDoc, schemaNode, "Name", cacheKey.SchemaName);
                }
                tableRootNode = schemaNode;
            }
            else
            {
                XmlNode sqlNode = connNode.SelectSingleNode("SelectSql[@Sql=\"" +
                                                            cacheKey.SelectStatement + "\"]");
                if (sqlNode == null)
                {
                    sqlNode = XmlDataUtil.AddElement(ref xDoc, connNode, "SelectSql");
                    XmlDataUtil.UpdateAttribute(ref xDoc, sqlNode, "Sql", cacheKey.SelectStatement);
                }
                tableRootNode = sqlNode;
            }
            if(tableRootNode.ChildNodes.Count>0)
            {
                if (string.IsNullOrEmpty(cacheKey.SelectStatement))
                {
                    XmlNodeList tblNodes = tableRootNode.SelectNodes("Table");
                    if(tblNodes !=null && tblNodes.Count>0)
                    {
                        foreach(XmlNode tblNode in tblNodes)
                        {
                            string tblName = XmlDataUtil.GetAttributeValue(tblNode, "Name", "");
                            XmlNodeList colNodes = tblNode.SelectNodes("Column");
                            if(colNodes !=null)
                            {
                                List<TableColumn> columns =new List<TableColumn>();
                                foreach(XmlNode colNode in colNodes)
                                {
                                    TableColumn col = new TableColumn();
                                    col.ColumnName = XmlDataUtil.GetAttributeValue(colNode, "ColumnName", "");
                                    col.DbType =
                                        (SqlDbType)
                                        int.Parse(XmlDataUtil.GetAttributeValue(colNode, "DataType",
                                                                                ((int) SqlDbType.VarChar).ToString()));
                                    col.Size = int.Parse(XmlDataUtil.GetAttributeValue(colNode, "Size", "255"));
                                    columns.Add(col);
                                }
                                dbSchema.Add(tblName,columns);
                            }
                        }
                    }
                }
                else
                {
                    XmlNodeList colNodes = tableRootNode.SelectNodes("Column");
                    if (colNodes != null)
                    {
                        List<TableColumn> columns = new List<TableColumn>();
                        foreach (XmlNode colNode in colNodes)
                        {
                            TableColumn col = new TableColumn();
                            col.ColumnName = XmlDataUtil.GetAttributeValue(colNode, "ColumnName", "");
                            col.DbType =
                                (SqlDbType)
                                int.Parse(XmlDataUtil.GetAttributeValue(colNode, "DataType",
                                                                        ((int)SqlDbType.VarChar).ToString()));
                            col.Size = int.Parse(XmlDataUtil.GetAttributeValue(colNode, "Size", "255"));
                            columns.Add(col);
                        }
                        dbSchema.Add("SQL", columns);
                    }
                }
            }
            else
            {
                if(string.IsNullOrEmpty(cacheKey.SelectStatement))
                {
                    string[] tblNames = DbUtil.GetTableNames(cacheKey.DbProvider, cacheKey.SchemaName, cacheKey.ConnStr);    
                    if(tblNames !=null && tblNames.Length>0)
                    {
                        foreach(string tblName in tblNames)
                        {
                            XmlNode tblNode = XmlDataUtil.AddElement(ref xDoc, tableRootNode, "Table");
                            TableColumn[] cols = DbUtil.GetTableColumns(cacheKey.DbProvider, cacheKey.ConnStr,
                                                                cacheKey.SelectStatement);
                            if (cols != null && cols.Length > 0)
                            {
                                foreach (TableColumn col in cols)
                                {
                                    XmlNode colNode = XmlDataUtil.AddElement(ref xDoc, tblNode, "Column");
                                    XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "ColumnName", col.ColumnName);
                                    XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "DataType", ((int)col.DbType).ToString());
                                    XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "Size", col.Size.ToString());
                                }
                                List<TableColumn> columns = new List<TableColumn>();
                                columns.AddRange(cols);
                                dbSchema.Add(tblName, columns);
                            }
                        }
                    }
                }
                else
                {
                    TableColumn[] cols = DbUtil.GetTableColumns(cacheKey.DbProvider, cacheKey.ConnStr,
                                                                cacheKey.SelectStatement);
                    if(cols !=null && cols.Length>0)
                    {
                        foreach(TableColumn col in cols)
                        {
                            XmlNode colNode = XmlDataUtil.AddElement(ref xDoc, tableRootNode, "Column");
                            XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "ColumnName", col.ColumnName);
                            XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "DataType", ((int)col.DbType).ToString());
                            XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "Size", col.Size.ToString());
                        }
                        List<TableColumn> columns=new List<TableColumn>();
                        columns.AddRange(cols);
                        dbSchema.Add("SQL", columns);
                    }
                }

            }
            
            return dbSchema;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <param name="tblName"></param>
        /// <param name="selectSql"></param>
        /// <returns></returns>
        public List<TableColumn> GetColumns(
            DbSchemaCacheKey cacheKey, string tblName, string selectSql)
        {
            List<TableColumn> cols=new List<TableColumn>();
            if (string.IsNullOrEmpty(cacheKey.ConnStr))
            {
                throw new Exception("DBSchema cache key must contain connection string");
            }
            XmlDocument xDoc = new XmlDocument();
            xDoc.Load(this.SchemaFilePath);
            XmlNode connNode =
                xDoc.SelectSingleNode("//Connection[@ConnectionString=\"" +
                                      cacheKey.ConnStr + "\"]");
            if (connNode == null)
            {
                XmlNode root = xDoc.DocumentElement;
                connNode = XmlDataUtil.AddElement(ref xDoc, root, "Connection");
                XmlDataUtil.UpdateAttribute(ref xDoc, connNode, "Name", "");
                XmlDataUtil.UpdateAttribute(ref xDoc, connNode, "ConnectionString", cacheKey.ConnStr);
                XmlDataUtil.UpdateAttribute(ref xDoc, connNode, "Provider", ((int)cacheKey.DbProvider).ToString());
            }
            XmlNode tableRootNode = null;
            if (string.IsNullOrEmpty(cacheKey.SelectStatement))
            {
                if (string.IsNullOrEmpty(cacheKey.SchemaName))
                    cacheKey.SchemaName = string.Empty;
                XmlNode schemaNode = connNode.SelectSingleNode("Schema[@Name=\"" + cacheKey.SchemaName + "\"]");
                if (schemaNode == null)
                {
                    schemaNode = XmlDataUtil.AddElement(ref xDoc, connNode, "Scehma");
                    XmlDataUtil.UpdateAttribute(ref xDoc, schemaNode, "Name", cacheKey.SchemaName);
                }
                tableRootNode = schemaNode;
            }
            else
            {
                XmlNode sqlNode = connNode.SelectSingleNode("SelectSql[@Sql=\"" +
                                                            cacheKey.SelectStatement + "\"]");
                if (sqlNode == null)
                {
                    sqlNode = XmlDataUtil.AddElement(ref xDoc, connNode, "SelectSql");
                    XmlDataUtil.UpdateAttribute(ref xDoc, sqlNode, "Sql", cacheKey.SelectStatement);
                }
                tableRootNode = sqlNode;
            }
            if(!string.IsNullOrEmpty(tblName))
            {
                XmlNode tblNode = tableRootNode.SelectSingleNode("Table[@Name=\"" + tblName + "\"]");
                if (tblNode != null)
                {
                    XmlNodeList colNodes = tblNode.SelectNodes("Column");
                    if (colNodes != null && colNodes.Count > 0)
                    {
                        foreach (XmlNode colNode in colNodes)
                        {
                            TableColumn col = new TableColumn();
                            col.ColumnName = XmlDataUtil.GetAttributeValue(colNode, "ColumnName", "");
                            col.DbType =
                                (SqlDbType)
                                int.Parse(
                                    XmlDataUtil.GetAttributeValue(colNode, "DataType",
                                                                  ((int) SqlDbType.VarChar).ToString()));
                            col.Size = int.Parse(XmlDataUtil.GetAttributeValue(colNode, "Size", "255"));
                            cols.Add(col);
                        }
                    }
                } 
            }
            else if(!string.IsNullOrEmpty(selectSql))
            {
                //XmlNode sqlNode = tableRootNode.SelectSingleNode("SelectSql[@Sql=\"" + selectSql + "\']");
                //if (sqlNode!=null)
                //{
                //    XmlNodeList colNodes = sqlNode.SelectNodes("Column");
                //    if (colNodes != null && colNodes.Count > 0)
                //    {
                //        foreach (XmlNode colNode in colNodes)
                //        {
                //            TableColumn col = new TableColumn();
                //            col.ColumnName = XmlDataUtil.GetAttributeValue(colNode, "ColumnName", "");
                //            col.DbType =
                //                (SqlDbType)
                //                int.Parse(
                //                    XmlDataUtil.GetAttributeValue(colNode, "DataType",
                //                                                  ((int)SqlDbType.VarChar).ToString()));
                //            col.Size = int.Parse(XmlDataUtil.GetAttributeValue(colNode, "Size", "255"));
                //            cols.Add(col);
                //        }
                //    }
                //}
            }
            
            if(cols.Count==0)
            {
                TableColumn[] tblCols = null;
                if (!string.IsNullOrEmpty(selectSql))
                {
                    tblCols = DbUtil.GetTableColumns(cacheKey.DbProvider, cacheKey.ConnStr, cacheKey.SelectStatement);
                }
                else if(!string.IsNullOrEmpty(tblName))
                {
                    tblCols = DbUtil.GetTableColumns(cacheKey.DbProvider, cacheKey.ConnStr, cacheKey.SchemaName, tblName);
                }
                if(tblCols !=null && tblCols.Length>0)
                {
                    cols.AddRange(tblCols);
                    List<string> objNames=new List<string>();
                    Dictionary<string,List<TableColumn>> newDbSchema =new Dictionary<string, List<TableColumn>>();
                    if(!string.IsNullOrEmpty(tblName))
                    {
                        objNames.Add(tblName);
                        newDbSchema.Add(tblName, cols);
                    }
                    else if(!string.IsNullOrEmpty(selectSql))
                    {
                        newDbSchema.Add("SQL", cols);
                    }
                    this.StoreDbSchema(cacheKey, objNames, newDbSchema);
                }
            }

            cols.Sort();
            return cols;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <returns></returns>
        public List<string> GetTables(DbSchemaCacheKey cacheKey)
        {
            if (string.IsNullOrEmpty(cacheKey.ConnStr))
            {
                throw new Exception("DBSchema cache key must contain connection string");
            }
            XmlDocument xDoc = new XmlDocument();
            xDoc.Load(this.SchemaFilePath);
            XmlNode connNode =
                xDoc.SelectSingleNode("//Connection[@ConnectionString=\"" +
                                      cacheKey.ConnStr + "\"]");
            if (connNode == null)
            {
                XmlNode root = xDoc.DocumentElement;
                connNode = XmlDataUtil.AddElement(ref xDoc, root, "Connection");
                XmlDataUtil.UpdateAttribute(ref xDoc, connNode, "Name", "");
                XmlDataUtil.UpdateAttribute(ref xDoc, connNode, "ConnectionString", cacheKey.ConnStr);
                XmlDataUtil.UpdateAttribute(ref xDoc, connNode, "Provider", ((int)cacheKey.DbProvider).ToString());
            }
            XmlNode tableRootNode = null;
            if (string.IsNullOrEmpty(cacheKey.SelectStatement))
            {
                if (string.IsNullOrEmpty(cacheKey.SchemaName))
                    cacheKey.SchemaName = string.Empty;
                XmlNode schemaNode = connNode.SelectSingleNode("Schema[@Name=\"" + cacheKey.SchemaName + "\"]");
                if (schemaNode == null)
                {
                    schemaNode = XmlDataUtil.AddElement(ref xDoc, connNode, "Scehma");
                    XmlDataUtil.UpdateAttribute(ref xDoc, schemaNode, "Name", cacheKey.SchemaName);
                }
                tableRootNode = schemaNode;
            }
            else
            {
                XmlNode sqlNode = connNode.SelectSingleNode("SelectSql[@Sql=\"" +
                                                            cacheKey.SelectStatement + "\"]");
                if (sqlNode == null)
                {
                    sqlNode = XmlDataUtil.AddElement(ref xDoc, connNode, "SelectSql");
                    XmlDataUtil.UpdateAttribute(ref xDoc, sqlNode, "Sql", cacheKey.SelectStatement);
                }
                tableRootNode = sqlNode;
            }
            List<string> tblNames=new List<string>();
            XmlNodeList tblNodes = tableRootNode.SelectNodes("Table");
            if(tblNodes !=null && tblNodes.Count>0)
            {
                foreach(XmlNode tblNode in tblNodes)
                {
                    string tblName = XmlDataUtil.GetAttributeValue(tblNode, "Name", "");
                    tblNames.Add(tblName);
                }
            }
            if(tblNames.Count==0)
            {
                string[] objNames = DbUtil.GetTableNames(cacheKey.DbProvider, cacheKey.SchemaName, cacheKey.ConnStr);
                if (objNames != null && objNames.Length > 0)
                {
                    tblNames.AddRange(objNames);
                    this.StoreDbSchema(cacheKey, tblNames, null);
                }
            }
            tblNames.Sort();
            return tblNames;
        }
    }
}
